{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>男1000米跑</th>\n",
       "      <th>男50米跑</th>\n",
       "      <th>男跳远</th>\n",
       "      <th>男体前屈</th>\n",
       "      <th>男引体</th>\n",
       "      <th>男肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.13</td>\n",
       "      <td>8.88</td>\n",
       "      <td>195.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2785.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>72.6</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.16</td>\n",
       "      <td>7.70</td>\n",
       "      <td>225.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>3133.0</td>\n",
       "      <td>174.0</td>\n",
       "      <td>52.7</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.09</td>\n",
       "      <td>8.45</td>\n",
       "      <td>218.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3901.0</td>\n",
       "      <td>169.0</td>\n",
       "      <td>46.5</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.21</td>\n",
       "      <td>8.05</td>\n",
       "      <td>206.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4946.0</td>\n",
       "      <td>183.0</td>\n",
       "      <td>79.7</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3.44</td>\n",
       "      <td>7.52</td>\n",
       "      <td>210.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>3538.0</td>\n",
       "      <td>171.0</td>\n",
       "      <td>54.7</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   班级 性别  男1000米跑  男50米跑    男跳远  男体前屈  男引体    男肺活量     身高    体重  BMI\n",
       "0   1  男     4.13   8.88  195.0  12.0  1.0  2785.0  170.0  72.6  0.0\n",
       "1   1  男     4.16   7.70  225.0  11.0  7.0  3133.0  174.0  52.7  0.0\n",
       "2   1  男     4.09   8.45  218.0  14.0  1.0  3901.0  169.0  46.5  0.0\n",
       "3   1  男     4.21   8.05  206.0  13.0  1.0  4946.0  183.0  79.7  0.0\n",
       "4   1  男     3.44   7.52  210.0  13.0  9.0  3538.0  171.0  54.7  0.0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>女800米跑</th>\n",
       "      <th>女50米跑</th>\n",
       "      <th>女跳远</th>\n",
       "      <th>女体前屈</th>\n",
       "      <th>女仰卧</th>\n",
       "      <th>女肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.22</td>\n",
       "      <td>9.32</td>\n",
       "      <td>185.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>3775.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>51.3</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>4.59</td>\n",
       "      <td>11.44</td>\n",
       "      <td>148.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>3683.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>66.6</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.46</td>\n",
       "      <td>13.40</td>\n",
       "      <td>150.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>3331.0</td>\n",
       "      <td>157.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.39</td>\n",
       "      <td>9.52</td>\n",
       "      <td>172.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>3701.0</td>\n",
       "      <td>160.0</td>\n",
       "      <td>50.7</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.43</td>\n",
       "      <td>9.79</td>\n",
       "      <td>145.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>3592.0</td>\n",
       "      <td>167.0</td>\n",
       "      <td>63.9</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   班级 性别  女800米跑  女50米跑    女跳远  女体前屈   女仰卧    女肺活量     身高    体重  BMI\n",
       "0   1  女    3.22   9.32  185.0  16.0  48.0  3775.0  163.0  51.3  0.0\n",
       "1   1  女    4.59  11.44  148.0   9.0  29.0  3683.0  163.0  66.6  0.0\n",
       "2   1  女    3.46  13.40  150.0   7.0  40.0  3331.0  157.0  60.0  0.0\n",
       "3   1  女    3.39   9.52  172.0  21.0  46.0  3701.0  160.0  50.7  0.0\n",
       "4   1  女    3.43   9.79  145.0   8.0  34.0  3592.0  167.0  63.9  0.0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#加载男女成绩表并转换数据类型\n",
    "male = pd.read_excel('C:/Users/srt/Desktop/2/python/模块四作业数据/18级高一体测成绩汇总.xls',sheet_name = \"男\",dtype = {'男50米跑':float,'男跳远':float,'男体前屈':float,'男引体':float,'男肺活量':float,'身高':float,'体重':float,'BMI':float})\n",
    "female = pd.read_excel('C:/Users/srt/Desktop/2/python/模块四作业数据/18级高一体测成绩汇总.xls',sheet_name = \"女\",dtype = {'女800米跑':float,'女50米跑':float,'女跳远':float,'女体前屈':float,'女仰卧':float,'女肺活量':float,'身高':float,'体重':float,'BMI':float})\n",
    "male['男1000米跑'] = male['男1000米跑'].replace(\"'\",\".\", regex=True).map(lambda x : float(x))\n",
    "display(male.head(),female.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>男肺活量成绩</th>\n",
       "      <th>男肺活量分数</th>\n",
       "      <th>女肺活量成绩</th>\n",
       "      <th>女肺活量分数</th>\n",
       "      <th>男50米跑成绩</th>\n",
       "      <th>男50米跑分数</th>\n",
       "      <th>女50米跑成绩</th>\n",
       "      <th>女50米跑分数</th>\n",
       "      <th>男体前屈成绩</th>\n",
       "      <th>男体前屈分数</th>\n",
       "      <th>...</th>\n",
       "      <th>女跳远成绩</th>\n",
       "      <th>女跳远分数</th>\n",
       "      <th>男引体成绩</th>\n",
       "      <th>男引体分数</th>\n",
       "      <th>女仰卧成绩</th>\n",
       "      <th>女仰卧分数</th>\n",
       "      <th>男1000米跑成绩</th>\n",
       "      <th>男1000米跑分数</th>\n",
       "      <th>女800米跑成绩</th>\n",
       "      <th>女800米跑分数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4540</td>\n",
       "      <td>100</td>\n",
       "      <td>3150</td>\n",
       "      <td>100</td>\n",
       "      <td>7.1</td>\n",
       "      <td>100</td>\n",
       "      <td>7.8</td>\n",
       "      <td>100</td>\n",
       "      <td>23.6</td>\n",
       "      <td>100</td>\n",
       "      <td>...</td>\n",
       "      <td>204</td>\n",
       "      <td>100</td>\n",
       "      <td>16.0</td>\n",
       "      <td>100</td>\n",
       "      <td>53</td>\n",
       "      <td>100</td>\n",
       "      <td>3'30\"</td>\n",
       "      <td>100</td>\n",
       "      <td>3'24\"</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4420</td>\n",
       "      <td>95</td>\n",
       "      <td>3100</td>\n",
       "      <td>95</td>\n",
       "      <td>7.2</td>\n",
       "      <td>95</td>\n",
       "      <td>7.9</td>\n",
       "      <td>95</td>\n",
       "      <td>21.5</td>\n",
       "      <td>95</td>\n",
       "      <td>...</td>\n",
       "      <td>198</td>\n",
       "      <td>95</td>\n",
       "      <td>15.0</td>\n",
       "      <td>95</td>\n",
       "      <td>51</td>\n",
       "      <td>95</td>\n",
       "      <td>3'35\"</td>\n",
       "      <td>95</td>\n",
       "      <td>3'30\"</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4300</td>\n",
       "      <td>90</td>\n",
       "      <td>3050</td>\n",
       "      <td>90</td>\n",
       "      <td>7.3</td>\n",
       "      <td>90</td>\n",
       "      <td>8.0</td>\n",
       "      <td>90</td>\n",
       "      <td>19.4</td>\n",
       "      <td>90</td>\n",
       "      <td>...</td>\n",
       "      <td>192</td>\n",
       "      <td>90</td>\n",
       "      <td>14.0</td>\n",
       "      <td>90</td>\n",
       "      <td>49</td>\n",
       "      <td>90</td>\n",
       "      <td>3'40\"</td>\n",
       "      <td>90</td>\n",
       "      <td>3'36\"</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4050</td>\n",
       "      <td>85</td>\n",
       "      <td>2900</td>\n",
       "      <td>85</td>\n",
       "      <td>7.4</td>\n",
       "      <td>85</td>\n",
       "      <td>8.3</td>\n",
       "      <td>85</td>\n",
       "      <td>17.2</td>\n",
       "      <td>85</td>\n",
       "      <td>...</td>\n",
       "      <td>185</td>\n",
       "      <td>85</td>\n",
       "      <td>13.0</td>\n",
       "      <td>85</td>\n",
       "      <td>46</td>\n",
       "      <td>85</td>\n",
       "      <td>3'47\"</td>\n",
       "      <td>85</td>\n",
       "      <td>3'43\"</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3800</td>\n",
       "      <td>80</td>\n",
       "      <td>2750</td>\n",
       "      <td>80</td>\n",
       "      <td>7.5</td>\n",
       "      <td>80</td>\n",
       "      <td>8.6</td>\n",
       "      <td>80</td>\n",
       "      <td>15.0</td>\n",
       "      <td>80</td>\n",
       "      <td>...</td>\n",
       "      <td>178</td>\n",
       "      <td>80</td>\n",
       "      <td>12.0</td>\n",
       "      <td>80</td>\n",
       "      <td>43</td>\n",
       "      <td>80</td>\n",
       "      <td>3'55\"</td>\n",
       "      <td>80</td>\n",
       "      <td>3'50\"</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   男肺活量成绩  男肺活量分数  女肺活量成绩  女肺活量分数  男50米跑成绩  男50米跑分数  女50米跑成绩  女50米跑分数  男体前屈成绩  \\\n",
       "0    4540     100    3150     100      7.1      100      7.8      100    23.6   \n",
       "1    4420      95    3100      95      7.2       95      7.9       95    21.5   \n",
       "2    4300      90    3050      90      7.3       90      8.0       90    19.4   \n",
       "3    4050      85    2900      85      7.4       85      8.3       85    17.2   \n",
       "4    3800      80    2750      80      7.5       80      8.6       80    15.0   \n",
       "\n",
       "   男体前屈分数    ...     女跳远成绩  女跳远分数  男引体成绩  男引体分数  女仰卧成绩  女仰卧分数  男1000米跑成绩  \\\n",
       "0     100    ...       204    100   16.0    100     53    100      3'30\"   \n",
       "1      95    ...       198     95   15.0     95     51     95      3'35\"   \n",
       "2      90    ...       192     90   14.0     90     49     90      3'40\"   \n",
       "3      85    ...       185     85   13.0     85     46     85      3'47\"   \n",
       "4      80    ...       178     80   12.0     80     43     80      3'55\"   \n",
       "\n",
       "   男1000米跑分数  女800米跑成绩  女800米跑分数  \n",
       "0        100     3'24\"       100  \n",
       "1         95     3'30\"        95  \n",
       "2         90     3'36\"        90  \n",
       "3         85     3'43\"        85  \n",
       "4         80     3'50\"        80  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 167,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#加载评分标准并转化数据类型\n",
    "standard = pd.read_excel('C:/Users/srt/Desktop/2/python/模块四作业数据/体侧成绩评分表.xls',header=[0,1],index_col=None)\n",
    "standard.columns =standard.columns.map(''.join)\n",
    "standard.reset_index(inplace=True)\n",
    "standard.rename(columns={'index':'男肺活量成绩'}, inplace = True)\n",
    "standard['男1000米跑成绩'] =standard['男1000米跑成绩'].replace([\"'\",'\"'],[\".\",\"\"], regex=True).map(lambda x : float(x))\n",
    "standard['女800米跑成绩'] =standard['女800米跑成绩'].replace([\"'\",'\"'],[\".\",\"\"], regex=True).map(lambda x : float(x))\n",
    "standard = standard.astype(\"float\")\n",
    "standard.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>女800米跑</th>\n",
       "      <th>女800米跑分数</th>\n",
       "      <th>女50米跑</th>\n",
       "      <th>女50米跑分数</th>\n",
       "      <th>女跳远</th>\n",
       "      <th>女跳远分数</th>\n",
       "      <th>女体前屈</th>\n",
       "      <th>女体前屈分数</th>\n",
       "      <th>女仰卧</th>\n",
       "      <th>女仰卧分数</th>\n",
       "      <th>女肺活量</th>\n",
       "      <th>女肺活量分数</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.22</td>\n",
       "      <td>100.0</td>\n",
       "      <td>9.32</td>\n",
       "      <td>72.0</td>\n",
       "      <td>185.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>3775.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>51.3</td>\n",
       "      <td>19.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>4.59</td>\n",
       "      <td>40.0</td>\n",
       "      <td>11.44</td>\n",
       "      <td>10.0</td>\n",
       "      <td>148.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>3683.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>66.6</td>\n",
       "      <td>25.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.46</td>\n",
       "      <td>80.0</td>\n",
       "      <td>13.40</td>\n",
       "      <td>0.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>3331.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>157.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>24.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.39</td>\n",
       "      <td>85.0</td>\n",
       "      <td>9.52</td>\n",
       "      <td>70.0</td>\n",
       "      <td>172.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>3701.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>160.0</td>\n",
       "      <td>50.7</td>\n",
       "      <td>19.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.43</td>\n",
       "      <td>85.0</td>\n",
       "      <td>9.79</td>\n",
       "      <td>68.0</td>\n",
       "      <td>145.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>3592.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>167.0</td>\n",
       "      <td>63.9</td>\n",
       "      <td>22.91</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   班级 性别  女800米跑 女800米跑分数  女50米跑 女50米跑分数    女跳远 女跳远分数  女体前屈 女体前屈分数   女仰卧  \\\n",
       "0   1  女    3.22    100.0   9.32    72.0  185.0  80.0  16.0   76.0  48.0   \n",
       "1   1  女    4.59     40.0  11.44    10.0  148.0  50.0   9.0   66.0  29.0   \n",
       "2   1  女    3.46     80.0  13.40     0.0  150.0  60.0   7.0   62.0  40.0   \n",
       "3   1  女    3.39     85.0   9.52    70.0  172.0  74.0  21.0   90.0  46.0   \n",
       "4   1  女    3.43     85.0   9.79    68.0  145.0  50.0   8.0   64.0  34.0   \n",
       "\n",
       "  女仰卧分数    女肺活量 女肺活量分数     身高    体重    BMI  \n",
       "0  85.0  3775.0  100.0  163.0  51.3  19.31  \n",
       "1  64.0  3683.0  100.0  163.0  66.6  25.07  \n",
       "2  76.0  3331.0  100.0  157.0  60.0  24.34  \n",
       "3  80.0  3701.0  100.0  160.0  50.7  19.80  \n",
       "4  70.0  3592.0  100.0  167.0  63.9  22.91  "
      ]
     },
     "execution_count": 170,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#因为平时excel用习惯了，第一反应是用vlookup模糊匹配，感觉跟分箱差不多，就用的分箱，没用题目里说的map，也没想明白应map该怎么用\n",
    "#感觉女生的这么算还行，男生的数据因为规则不够统一就有点麻烦\n",
    "#处理女生数据\n",
    "for i in ['女800米跑', '女50米跑']:\n",
    "    a = list(standard[i + '成绩'])\n",
    "    a.insert(0,0.0)\n",
    "    a.append(100.0)\n",
    "    b = list(standard[i + '分数'])\n",
    "    b.append(0.0)\n",
    "    female[i + '分数'] = pd.cut(female[i],\n",
    "                               bins = a,\n",
    "                               labels = b)\n",
    "for i in ['女跳远', '女体前屈', '女仰卧', '女肺活量']:\n",
    "    a = list(standard[i + '成绩'])\n",
    "    a.reverse()\n",
    "    a.insert(0,0.0)\n",
    "    a.append(10000.0)\n",
    "    b = list(standard[i + '分数'])\n",
    "    b.reverse()\n",
    "    b.insert(0,0.0)\n",
    "    female[i + '分数'] = pd.cut(female[i],\n",
    "                           bins = a,\n",
    "                           labels = b)\n",
    "order_f =['班级', '性别', '女800米跑', '女800米跑分数','女50米跑','女50米跑分数', '女跳远', '女跳远分数', \n",
    "        '女体前屈', '女体前屈分数', '女仰卧',  '女仰卧分数','女肺活量','女肺活量分数', '身高','体重', 'BMI']\n",
    "female = female.reindex(columns = order_f)\n",
    "female['BMI']= female.apply(lambda x: round(x['体重']/x['身高']/x['身高']*10000,2) if x['身高']>0 else 0,axis = 1)\n",
    "female.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>男1000米跑</th>\n",
       "      <th>男50米跑</th>\n",
       "      <th>男跳远</th>\n",
       "      <th>男体前屈</th>\n",
       "      <th>男引体</th>\n",
       "      <th>男肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "      <th>男1000米跑分数</th>\n",
       "      <th>男50米跑分数</th>\n",
       "      <th>男跳远分数</th>\n",
       "      <th>男肺活量分数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.13</td>\n",
       "      <td>8.88</td>\n",
       "      <td>195.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2785.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>72.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>62.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.16</td>\n",
       "      <td>7.70</td>\n",
       "      <td>225.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>3133.0</td>\n",
       "      <td>174.0</td>\n",
       "      <td>52.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>68.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.09</td>\n",
       "      <td>8.45</td>\n",
       "      <td>218.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3901.0</td>\n",
       "      <td>169.0</td>\n",
       "      <td>46.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.21</td>\n",
       "      <td>8.05</td>\n",
       "      <td>206.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4946.0</td>\n",
       "      <td>183.0</td>\n",
       "      <td>79.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3.44</td>\n",
       "      <td>7.52</td>\n",
       "      <td>210.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>3538.0</td>\n",
       "      <td>171.0</td>\n",
       "      <td>54.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>74.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   班级 性别  男1000米跑  男50米跑    男跳远  男体前屈  男引体    男肺活量     身高    体重  BMI  \\\n",
       "0   1  男     4.13   8.88  195.0  12.0  1.0  2785.0  170.0  72.6  0.0   \n",
       "1   1  男     4.16   7.70  225.0  11.0  7.0  3133.0  174.0  52.7  0.0   \n",
       "2   1  男     4.09   8.45  218.0  14.0  1.0  3901.0  169.0  46.5  0.0   \n",
       "3   1  男     4.21   8.05  206.0  13.0  1.0  4946.0  183.0  79.7  0.0   \n",
       "4   1  男     3.44   7.52  210.0  13.0  9.0  3538.0  171.0  54.7  0.0   \n",
       "\n",
       "  男1000米跑分数 男50米跑分数 男跳远分数 男肺活量分数  \n",
       "0      72.0    66.0  50.0   62.0  \n",
       "1      70.0    78.0  74.0   68.0  \n",
       "2      74.0    70.0  70.0   80.0  \n",
       "3      68.0    74.0  64.0  100.0  \n",
       "4      85.0    78.0  66.0   74.0  "
      ]
     },
     "execution_count": 171,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#处理男生数据\n",
    "for i in ['男1000米跑', '男50米跑']:\n",
    "    a = list(standard[i + '成绩'])\n",
    "    a.insert(0,0.0)\n",
    "    a.append(100.0)\n",
    "    b = list(standard[i + '分数'])\n",
    "    b.append(0.0)\n",
    "    male[i + '分数'] = pd.cut(male[i],\n",
    "                               bins = a,\n",
    "                               labels = b)\n",
    "for i in ['男跳远', '男肺活量']:\n",
    "    a = list(standard[i + '成绩'])\n",
    "    a.reverse()\n",
    "    a.insert(0,0.0)\n",
    "    a.append(10000.0)\n",
    "    b = list(standard[i + '分数'])\n",
    "    b.reverse()\n",
    "    b.insert(0,0.0)\n",
    "    male[i + '分数'] = pd.cut(male[i],\n",
    "                           bins = a,\n",
    "                           labels = b)\n",
    "male.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>男1000米跑</th>\n",
       "      <th>男1000米跑分数</th>\n",
       "      <th>男50米跑</th>\n",
       "      <th>男50米跑分数</th>\n",
       "      <th>男跳远</th>\n",
       "      <th>男跳远分数</th>\n",
       "      <th>男体前屈</th>\n",
       "      <th>男体前屈分数</th>\n",
       "      <th>男引体</th>\n",
       "      <th>男引体分数</th>\n",
       "      <th>男肺活量</th>\n",
       "      <th>男肺活量分数</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.13</td>\n",
       "      <td>72.0</td>\n",
       "      <td>8.88</td>\n",
       "      <td>66.0</td>\n",
       "      <td>195.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2785.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>72.6</td>\n",
       "      <td>25.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.16</td>\n",
       "      <td>70.0</td>\n",
       "      <td>7.70</td>\n",
       "      <td>78.0</td>\n",
       "      <td>225.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>3133.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>174.0</td>\n",
       "      <td>52.7</td>\n",
       "      <td>17.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.09</td>\n",
       "      <td>74.0</td>\n",
       "      <td>8.45</td>\n",
       "      <td>70.0</td>\n",
       "      <td>218.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3901.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>169.0</td>\n",
       "      <td>46.5</td>\n",
       "      <td>16.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.21</td>\n",
       "      <td>68.0</td>\n",
       "      <td>8.05</td>\n",
       "      <td>74.0</td>\n",
       "      <td>206.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4946.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>183.0</td>\n",
       "      <td>79.7</td>\n",
       "      <td>23.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3.44</td>\n",
       "      <td>85.0</td>\n",
       "      <td>7.52</td>\n",
       "      <td>78.0</td>\n",
       "      <td>210.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>3538.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>171.0</td>\n",
       "      <td>54.7</td>\n",
       "      <td>18.71</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   班级 性别  男1000米跑 男1000米跑分数  男50米跑 男50米跑分数    男跳远 男跳远分数  男体前屈  男体前屈分数  男引体  \\\n",
       "0   1  男     4.13      72.0   8.88    66.0  195.0  50.0  12.0    74.0  1.0   \n",
       "1   1  男     4.16      70.0   7.70    78.0  225.0  74.0  11.0    74.0  7.0   \n",
       "2   1  男     4.09      74.0   8.45    70.0  218.0  70.0  14.0    78.0  1.0   \n",
       "3   1  男     4.21      68.0   8.05    74.0  206.0  64.0  13.0    76.0  1.0   \n",
       "4   1  男     3.44      85.0   7.52    78.0  210.0  66.0  13.0    76.0  9.0   \n",
       "\n",
       "  男引体分数    男肺活量 男肺活量分数     身高    体重    BMI  \n",
       "0   0.0  2785.0   62.0  170.0  72.6  25.12  \n",
       "1  50.0  3133.0   68.0  174.0  52.7  17.41  \n",
       "2   0.0  3901.0   80.0  169.0  46.5  16.28  \n",
       "3   0.0  4946.0  100.0  183.0  79.7  23.80  \n",
       "4  64.0  3538.0   74.0  171.0  54.7  18.71  "
      ]
     },
     "execution_count": 172,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#因为引体成绩里有空数据，所以单独处理了一下\n",
    "c = standard[standard['男引体成绩'].notnull()]\n",
    "a = list(c['男引体成绩'])\n",
    "a.reverse()\n",
    "a.insert(0,0.0)\n",
    "a.append(10000.0)\n",
    "b = list(c['男引体分数'])\n",
    "b.reverse()\n",
    "b.insert(0,0.0)\n",
    "male['男引体分数'] = pd.cut(male['男引体'],\n",
    "                       bins = a,\n",
    "                       labels = b)\n",
    "#因为体前屈成绩最小值小于了0，所以单独处理\n",
    "a = list(standard['男体前屈成绩'])\n",
    "a.reverse()\n",
    "a.insert(0,-100.0)\n",
    "a.append(10000.0)\n",
    "b = list(standard['男体前屈分数'])\n",
    "b.reverse()\n",
    "b.insert(0,0.0)\n",
    "male['男体前屈分数'] = pd.cut(male['男体前屈'],\n",
    "                       bins = a,\n",
    "                       labels = b)\n",
    "male['男体前屈分数']= male.apply(lambda x: 0 if x['男体前屈']==0 else x['男体前屈分数'],axis = 1)\n",
    "#重新排序\n",
    "order_m =['班级', '性别', '男1000米跑', '男1000米跑分数','男50米跑','男50米跑分数', '男跳远', '男跳远分数', \n",
    "        '男体前屈', '男体前屈分数', '男引体',  '男引体分数','男肺活量','男肺活量分数', '身高','体重', 'BMI']\n",
    "male = male.reindex(columns = order_m)\n",
    "male['BMI']= male.apply(lambda x: round(x['体重']/x['身高']/x['身高']*10000,2) if x['身高']>0 else 0,axis = 1)\n",
    "male.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "metadata": {},
   "outputs": [],
   "source": [
    "with pd.ExcelWriter(r'C:/Users/srt/Desktop/2/python/模块四作业数据/分数.xlsx') as writer:\n",
    "    male.to_excel(writer, sheet_name='male', index = False)\n",
    "    female.to_excel(writer, sheet_name='female', index = False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.0"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
